﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;

namespace MTPAutoBackup
{
    public class DbUtil
    {
        private string connectionString;
        private string dbPath;
        private SQLiteConnection selfConnection;

        public DbUtil()
        {
            dbPath = ConfigUtil.GetDBPath();
            //"Data Source=sample.db;Version=3;"
            connectionString = "Data Source=" + dbPath + ";Version=3;";
        }

        public SQLiteConnection Connect()
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            return connection;
        }

        public SQLiteConnection GetSelfConnect()
        {
            if (selfConnection == null)
            {
                selfConnection = new SQLiteConnection(connectionString);
            }

            if (selfConnection.State == System.Data.ConnectionState.Closed)
            {
                selfConnection.Open();
                // 执行数据库操作
            }
            else if (selfConnection.State == System.Data.ConnectionState.Open)
            {
                // 连接已经打开，可以继续执行数据库操作
            }

            return selfConnection;
        }

        public void Close()
        {
            if (selfConnection != null)
            {
                selfConnection.Close();
            }
        }

        public void CreateDBAndTable()
        {
            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                string dbFullPath = Path.GetFullPath(dbPath);
                bool exists = File.Exists(dbFullPath);
                string directoryPath = Path.GetDirectoryName(dbFullPath);
                if (!Directory.Exists(directoryPath))
                {
                    Directory.CreateDirectory(directoryPath);
                    Console.WriteLine($"Created directory: {directoryPath}");
                }

                connection.Open();
                if (!exists)
                {
                    // 创建表
                    CreateTable(connection);
                }
            }
        }

        public void InsertData(List<FileInfo> fileInfoList)
        {
            if (!fileInfoList.Any())
            {
                return;
            }

            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                connection.Open();

                string insertQuery = """
                                     INSERT INTO FILES (
                                     ID,DEVICE_ID,NAME,REMOTE_PATH,DIRECTORY_PATH,IS_DIRECTORY,SIZE,DISPLAY_SIZE,CREATE_DATE,UPDATE_DATE,SORT,IS_LOAD,DELETE_FLAG
                                     ) VALUES (
                                     @ID,@DEVICE_ID,@NAME,@REMOTE_PATH,@DIRECTORY_PATH,@IS_DIRECTORY,@SIZE,@DISPLAY_SIZE,@CREATE_DATE,@UPDATE_DATE,@SORT,@IS_LOAD,@DELETE_FLAG
                                     );
                                     """;
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
                    {
                        command.Parameters.Add(new SQLiteParameter("@ID"));
                        command.Parameters.Add(new SQLiteParameter("@DEVICE_ID"));
                        command.Parameters.Add(new SQLiteParameter("@NAME"));
                        command.Parameters.Add(new SQLiteParameter("@REMOTE_PATH"));
                        command.Parameters.Add(new SQLiteParameter("@DIRECTORY_PATH"));
                        command.Parameters.Add(new SQLiteParameter("@IS_DIRECTORY"));
                        command.Parameters.Add(new SQLiteParameter("@SIZE"));
                        command.Parameters.Add(new SQLiteParameter("@DISPLAY_SIZE"));
                        command.Parameters.Add(new SQLiteParameter("@CREATE_DATE"));
                        command.Parameters.Add(new SQLiteParameter("@UPDATE_DATE"));
                        command.Parameters.Add(new SQLiteParameter("@SORT"));
                        command.Parameters.Add(new SQLiteParameter("@IS_LOAD"));
                        command.Parameters.Add(new SQLiteParameter("@DELETE_FLAG"));

                        foreach (FileInfo info in fileInfoList)
                        {
                            command.Parameters["@ID"].Value = info.Id;
                            command.Parameters["@DEVICE_ID"].Value = info.DeviceId;
                            command.Parameters["@NAME"].Value = info.Name;
                            command.Parameters["@REMOTE_PATH"].Value = info.RemotePath;
                            command.Parameters["@DIRECTORY_PATH"].Value = info.DirectoryPath;
                            command.Parameters["@IS_DIRECTORY"].Value = info.IsDirectory;
                            command.Parameters["@SIZE"].Value = info.Size;
                            command.Parameters["@DISPLAY_SIZE"].Value = info.DisplaySize;
                            command.Parameters["@CREATE_DATE"].Value = info.CreateDate;
                            command.Parameters["@UPDATE_DATE"].Value = info.LastModified;
                            command.Parameters["@SORT"].Value = info.Sort;
                            command.Parameters["@IS_LOAD"].Value = 0;
                            command.Parameters["@DELETE_FLAG"].Value = "0";

                            command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
        }


        //新增设备信息
        public void InsertDeviceInfo(DeviceInfo info)
        {
            DeviceInfo queryDeviceInfo = QueryDeviceInfo(info.SerialNumber);
            if (queryDeviceInfo != null)
            {
                return;
            }

            using (SQLiteConnection connection = Connect())
            {
                connection.Open();

                // // 根据 SERIAL_NUMBER 查询是否存在数据
                // string checkQuery = "SELECT COUNT(*) FROM DEVICES WHERE SERIAL_NUMBER = @SERIAL_NUMBER";
                // using (SQLiteCommand checkCommand = new SQLiteCommand(checkQuery, connection))
                // {
                //     checkCommand.Parameters.Add(new SQLiteParameter("@SERIAL_NUMBER", info.SerialNumber));
                //     int count = Convert.ToInt32(checkCommand.ExecuteScalar());
                //     if (count > 0)
                //     {
                //         Console.WriteLine("数据已存在，不进行新增。");
                //         return;
                //     }
                // }

                // 查询 SORT 的最大值
                string maxSortQuery = "SELECT MAX(SORT) FROM DEVICES";
                int maxSort = 0;
                using (SQLiteCommand maxSortCommand = new SQLiteCommand(maxSortQuery, connection))
                {
                    object result = maxSortCommand.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        maxSort = Convert.ToInt32(result);
                    }
                }

                int sort = maxSort + 1;
                string maxSortStr = sort.ToString().PadLeft(3, '0');
                info.Sort = sort;
                info.Id = maxSortStr;
                info.PathName = "BAK" + maxSortStr;

                string insertQuery = "INSERT INTO DEVICES (ID,SORT,NAME,PATH_NAME,MODEL,SERIAL_NUMBER,DEVICE_TYPE,DELETE_FLAG) VALUES (@ID,@SORT,@NAME,@PATH_NAME,@MODEL,@SERIAL_NUMBER,@DEVICE_TYPE,@DELETE_FLAG);";
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
                    {
                        //设置参数
                        command.Parameters.Add(new SQLiteParameter("@ID"));
                        command.Parameters.Add(new SQLiteParameter("@SORT"));
                        command.Parameters.Add(new SQLiteParameter("@NAME"));
                        command.Parameters.Add(new SQLiteParameter("@PATH_NAME"));
                        command.Parameters.Add(new SQLiteParameter("@MODEL"));
                        command.Parameters.Add(new SQLiteParameter("@SERIAL_NUMBER"));
                        command.Parameters.Add(new SQLiteParameter("@DEVICE_TYPE"));
                        command.Parameters.Add(new SQLiteParameter("@DELETE_FLAG"));

                        //赋值
                        command.Parameters["@ID"].Value = info.Id;
                        command.Parameters["@SORT"].Value = info.Sort;
                        command.Parameters["@NAME"].Value = info.Name;
                        command.Parameters["@PATH_NAME"].Value = info.PathName;
                        command.Parameters["@MODEL"].Value = info.Model;
                        command.Parameters["@SERIAL_NUMBER"].Value = info.SerialNumber;
                        command.Parameters["@DEVICE_TYPE"].Value = info.DeviceType;
                        command.Parameters["@DELETE_FLAG"].Value = info.DeleteFlag;

                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }

                Console.WriteLine("Data inserted successfully.");
            }
        }

        //查询设备信息
        public DeviceInfo QueryDeviceInfo(string serialNumber)
        {
            DeviceInfo info = null;

            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                connection.Open();

                string selectQuery = "SELECT * FROM DEVICES WHERE SERIAL_NUMBER=@SERIAL_NUMBER;";
                using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
                {
                    // 设置参数
                    command.Parameters.AddWithValue("@SERIAL_NUMBER", serialNumber);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("Query results:");
                        if (reader.Read())
                        {
                            info = new DeviceInfo();

                            info.Id = reader["ID"].ToString();
                            info.Sort = int.Parse(reader["SORT"].ToString());
                            info.Name = reader["NAME"].ToString();
                            info.PathName = reader["PATH_NAME"].ToString();
                            info.Model = reader["MODEL"].ToString();
                            info.SerialNumber = reader["SERIAL_NUMBER"].ToString();
                            info.DeviceType = reader["DEVICE_TYPE"].ToString();
                            info.DeleteFlag = reader["DELETE_FLAG"].ToString();
                        }
                    }
                }
            }

            return info;
        }

        public List<FileInfo> QueryAllData(string deviceId)
        {
            List<FileInfo> allFileInfoList = new List<FileInfo>();

            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                connection.Open();


                string selectQuery = "SELECT * FROM FILES WHERE DEVICE_ID = @DEVICE_ID AND DELETE_FLAG = '0';";
                using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
                {
                    // 设置参数
                    command.Parameters.AddWithValue("@DEVICE_ID", deviceId);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("Query results:");
                        while (reader.Read())
                        {
                            FileInfo info = new FileInfo();

                            info.Id = reader["ID"].ToString();
                            info.DeviceId = reader["DEVICE_ID"].ToString();
                            info.Name = reader["NAME"].ToString();
                            info.RemotePath = reader["REMOTE_PATH"].ToString();
                            info.DirectoryPath = reader["DIRECTORY_PATH"].ToString();
                            info.IsDirectory = reader["IS_DIRECTORY"].ToString().Equals("1");
                            info.Size = ulong.Parse(reader["SIZE"].ToString());
                            info.DisplaySize = reader["DISPLAY_SIZE"].ToString();
                            info.CreateDate = reader["CREATE_DATE"].ToString();
                            info.LastModified = reader["UPDATE_DATE"].ToString();
                            info.Sort = ulong.Parse(reader["SORT"].ToString());
                            info.IsLoad = reader["IS_LOAD"].Equals("1");
                            info.DeleteFlag = reader["DELETE_FLAG"].ToString();

                            allFileInfoList.Add(info);
                        }
                    }
                }
            }

            return allFileInfoList;
        }

        public List<FileInfo> QueryLoadList(string deviceId)
        {
            List<FileInfo> allFileInfoList = new List<FileInfo>();

            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                connection.Open();


                string selectQuery = "SELECT * FROM FILES WHERE IS_LOAD = 0 AND DEVICE_ID = @DEVICE_ID AND DELETE_FLAG = '0';";
                using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
                {
                    // 设置参数
                    command.Parameters.AddWithValue("@DEVICE_ID", deviceId);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("Query results:");
                        while (reader.Read())
                        {
                            FileInfo info = new FileInfo();

                            info.Id = reader["ID"].ToString();
                            info.DeviceId = reader["DEVICE_ID"].ToString();
                            info.Name = reader["NAME"].ToString();
                            info.RemotePath = reader["REMOTE_PATH"].ToString();
                            info.DirectoryPath = reader["DIRECTORY_PATH"].ToString();
                            info.IsDirectory = reader["IS_DIRECTORY"].ToString().Equals("1");
                            info.Size = ulong.Parse(reader["SIZE"].ToString());
                            info.DisplaySize = reader["DISPLAY_SIZE"].ToString();
                            info.CreateDate = reader["CREATE_DATE"].ToString();
                            info.LastModified = reader["UPDATE_DATE"].ToString();
                            info.Sort = ulong.Parse(reader["SORT"].ToString());
                            info.IsLoad = reader["IS_LOAD"].Equals("1");
                            info.DeleteFlag = reader["DELETE_FLAG"].ToString();

                            allFileInfoList.Add(info);
                        }
                    }
                }
            }

            return allFileInfoList;
        }

        public ulong QueryAllCount()
        {
            // 定义一个变量来保存查询到的记录数
            ulong count = 0;

            // 创建数据库连接
            using (SQLiteConnection connection = Connect())
            {
                connection.Open();
                string selectQuery = "SELECT COUNT(*) FROM FILES;";
                using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
                {
                    // 使用 ExecuteScalar 来执行返回单个值的查询
                    count = Convert.ToUInt64(command.ExecuteScalar());
                    //转换 ulong  Convert.ToUInt64
                    //转换 long  Convert.ToInt64
                    //转换 int  Convert.ToInt32
                }
            }

            // 返回查询到的记录总数
            return count;
        }


        public void UpdateDataBacked(string id)
        {
            // 获取当前时间，并格式化为字符串
            string formattedDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); // 根据需要修改格式

            // 获取数据库连接
            SQLiteConnection connection = GetSelfConnect();

            // 更新语句：将 BACKUP_DATE 更新为格式化时间，IS_LOAD 更新为 1
            string updateQuery = "UPDATE FILES SET BACKUP_DATE = @BackupDate, IS_LOAD = 1 WHERE ID = @ID;";

            using (SQLiteCommand command = new SQLiteCommand(updateQuery, connection))
            {
                // 设置参数
                command.Parameters.AddWithValue("@BackupDate", formattedDate);
                command.Parameters.AddWithValue("@ID", id); // 使用传入的 id 变量

                // 执行更新操作
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }

        public void LogicalDeleteData(string id)
        {
            // 获取当前时间，并格式化为字符串
            string formattedDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); // 根据需要修改格式

            // 获取数据库连接
            var connection = GetSelfConnect();

            // 更新语句：将 DELETE_FLAG 更新为当前时间字符串
            string updateQuery = "UPDATE FILES SET DELETE_FLAG = @DeleteFlag WHERE ID = @ID;";

            using (SQLiteCommand command = new SQLiteCommand(updateQuery, connection))
            {
                // 设置参数
                command.Parameters.AddWithValue("@DeleteFlag", formattedDate);
                command.Parameters.AddWithValue("@ID", id); // 使用传入的 id 变量

                // 执行更新操作
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) marked as deleted.");
            }
        }


        public void OpenDb()
        {
            // 创建数据库连接
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 创建表
                CreateTable(connection);

                // 批量插入数据
                InsertData(connection);

                // 查询数据
                QueryData(connection);

                // 更新数据
                UpdateData(connection);

                // 查询更新后的数据
                QueryData(connection);

                connection.Close();
            }
        }


        private static void CreateTable(SQLiteConnection connection)
        {
            string createTableQuery = """
                                      CREATE TABLE IF NOT EXISTS "FILES" (
                                      "ID"	TEXT,
                                      "DEVICE_ID"	TEXT,
                                      "NAME"	TEXT,
                                      "REMOTE_PATH"	TEXT,
                                      "DIRECTORY_PATH"	TEXT,
                                      "IS_DIRECTORY"	INTEGER,
                                      "SIZE"	NUMERIC,
                                      "DISPLAY_SIZE"	TEXT,
                                      "CREATE_DATE"	TEXT,
                                      "UPDATE_DATE"	TEXT,
                                      "BACKUP_DATE"	TEXT,
                                      "SORT"	NUMERIC,
                                      "STATE"	TEXT,
                                      "IS_LOAD"	INTEGER,
                                      "DELETE_FLAG"	TEXT
                                      );
                                      """;
            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
                Console.WriteLine("Table created successfully.");
            }

            //创建 设备信息表 Devices
            string createDevicesTable = """
                                        CREATE TABLE IF NOT EXISTS "DEVICES" (
                                        "ID"	TEXT,
                                        "SORT"	INTEGER,
                                        "NAME"	TEXT,
                                        "PATH_NAME"	TEXT,
                                        "MODEL"	TEXT,
                                        "SERIAL_NUMBER"	TEXT,
                                        "DEVICE_TYPE"	TEXT,
                                        "DELETE_FLAG"	TEXT
                                        );
                                        """;
            using (SQLiteCommand command = new SQLiteCommand(createDevicesTable, connection))
            {
                command.ExecuteNonQuery();
                Console.WriteLine("Devices Table created successfully.");
            }
        }

        static void InsertData(SQLiteConnection connection)
        {
            string insertQuery = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age);";
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
                {
                    command.Parameters.Add(new SQLiteParameter("@Name"));
                    command.Parameters.Add(new SQLiteParameter("@Age"));

                    for (int i = 0; i < 10; i++)
                    {
                        command.Parameters["@Name"].Value = $"User_{i + 1}";
                        command.Parameters["@Age"].Value = 20 + i;

                        command.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }

            Console.WriteLine("Data inserted successfully.");
        }

        static void QueryData(SQLiteConnection connection)
        {
            string selectQuery = "SELECT * FROM Users;";
            using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("Query results:");
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
                    }
                }
            }
        }

        private static void UpdateData(SQLiteConnection connection)
        {
            string updateQuery = "UPDATE Users SET Age = Age + 1 WHERE Name = @Name;";
            using (SQLiteCommand command = new SQLiteCommand(updateQuery, connection))
            {
                command.Parameters.AddWithValue("@Name", "User_1");
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}